/*
 * this class contains all operations needed to deal with restaurant. It extends
 * DataAccessLayer class which contains more general methods to handle all
 * kind of beans and tables.
 *
 */
package webapp.model;

import java.sql.*;
import java.util.*;
/**
 *
 * @author Nina
 * @author Xiao (search methods)
 */
public class RestaurantManager extends DataAccessLayer
{
//........................ D A T A   F I E L D S ............................//
    static final int SEARCH_LIMIT = 100;
    static final int PAGE_LIMIT = 7;

//........................ C O N S T R U C T O R S ..........................//

    public RestaurantManager()
    {   } // end of the constructor

//...................... P U B L I C   M E T H O D S ........................//

/*   *//** addRecipe
     * Adds a recipe to the DB using the DAL
     * @param r - recipe to be added. ID will be overwritten during insertion.
     * @returns the recipe with a modified ID based on insertion in the DB.
     *//*
    public Restaurant addRestaurant(Restaurant r) {

        int result = -1;
        try {
            result = insertRestaurant(r);
        } catch (Exception e) 
        { System.err.println(e.getMessage()); }
        r.setId(result);
        return r;
    }
*/



    /**
     * return a Recipe for a specific recipe id
     * @param recipe_id - the recipe id
     * @return the recipe.
     */
    public Restaurant getRestaurantById(int restaurant_id)
    {
        String stmnt = String.format("SELECT * FROM restaurant WHERE restaurant_ID = %d LIMIT 1",
        		restaurant_id);

        ResultSet rs = executeQuery(stmnt);
        ArrayList<Restaurant> arr = resultSetPacker(rs, Restaurant.class);

        return arr.get(0);

    } // end of the main method.




    /**
     * get the recommend restaurants.
     * @return - the array of the restaurants
     */
    public Restaurant[] getRecommendRestaurant()
    {
        String stmnt = String.format(
                "SELECT * FROM restaurant LIMIT %d ",
                PAGE_LIMIT);
        ResultSet rs = executeQuery(stmnt);
        ArrayList<Restaurant> arr = resultSetPacker(rs, Restaurant.class);

        return arr.toArray(new Restaurant[arr.size()]);

    } // end of the method


 


    /**
     * get the most favorite recipes.
     * @return - the array of the recipes
     */
    public Restaurant[] getRestaurantByType(String type)
    {
        String stmnt = String.format(
                "select * from restaurant as r JOIN type as t on r.type_ID = t.type_id WHERE t.name = "+type+" LIMIT %d ",
                PAGE_LIMIT);
        ResultSet rs = executeQuery(stmnt);
        ArrayList<Restaurant> arr = resultSetPacker(rs, Restaurant.class);

        return arr.toArray(new Restaurant[arr.size()]);

    } // end of the method


    public Restaurant[] getFavoriteRestaurants()
    {
        String stmnt = String.format(
                "SELECT * FROM restaurant ORDER BY rating DESC LIMIT %d ",
                PAGE_LIMIT);
        ResultSet rs = executeQuery(stmnt);
        ArrayList<Restaurant> arr = resultSetPacker(rs, Restaurant.class);

        return arr.toArray(new Restaurant[arr.size()]);

    } // end of the method
    
    public boolean updateRatingByRestaurantId (String newRate, int recipeId)
    {
        String stmnt = String.format(
                "UPDATE restaurant "
                + "SET rating = %d "
                + "WHERE restaurant_ID = %d", newRate, recipeId);

        //Test!
        System.out.println("Statement: \n" + stmnt);

        int result = executeUpdate(stmnt);

        if (result == -1) return false;
        else              return true;

    } // end of the method

	public ResultSet search(String type, String price, String rating, String keywords) {
        StringBuffer cond=new StringBuffer();
        if(type!=null && !type.trim().isEmpty() && Integer.parseInt(type)<=6 && Integer.parseInt(type)>0){
        	cond.append("TYPE_ID=").append(type);
        }
        if(price!=null && !price.trim().isEmpty() && "$$$$$".contains(price)){
        	cond.append(cond.length()==0?"":" AND ").append("price_range LIKE '%").append(price).append('\'');
        }
        
       if(rating!=null && !rating.trim().isEmpty() && rating.length()==1 && "012345".contains(rating)){
        	cond.append(cond.length()==0?"":" AND ").append("rating >= ").append(rating);
        }
	
       if(keywords!=null && !keywords.trim().isEmpty()){
    	   //TODO: sql escape
    	   keywords="'%"+keywords.trim()+"%'";
    	   cond.append(cond.length()==0?"":" AND ")
    	   	.append(String.format("( name LIKE %s OR ADDRESS LIKE %s OR website LIKE %s )", keywords, keywords, keywords));
       } 
       String stmnt = String.format(
                "select * from restaurant %sLIMIT %d", ((cond.length()==0)?"":(" WHERE "+cond.toString()+" ")),
                PAGE_LIMIT);
        ResultSet rs = executeQuery(stmnt);
		return rs;
	}
    
  /*  private void testgetRestaurantByType(String type)
    {
        Restaurant[] arr = getRestaurantByType("Chinese");

        showObject(arr);

    } // end of the method.


    *//**
     * get recipe objects by recipe ID method 
     * @param recipeId array of recipe IDs
     * @return array of Recipe objects
     *//*
    public ArrayList<Recipe> getRecipesByRecipeId(ArrayList<Integer> recipeId)
    {
        ArrayList<Recipe> recipesArr = new ArrayList<Recipe>();

        for (int i = 0; i < recipeId.size(); i++)
            recipesArr.add(getRecipeById(recipeId.get(i)));

        return recipesArr;
    } // end of the method


    *//**
     * return all Recipes for  a specific user id
     * @param user_id - the recipe id
     * @return the array list of the recipes.
     *//*
    public ArrayList<Recipe> getRecipeByUserID(int user_id)
    {
        String stmnt =
                String.format("SELECT * FROM recipe WHERE user_id = %d", user_id);

        ResultSet rs = executeQuery(stmnt);
        ArrayList<Recipe> arr = resultSetPacker(rs, Recipe.class);

        return arr;

    } // end of the main method.

    private void testgetRecipeByUserID()
            throws ClassNotFoundException, SQLException,
            InstantiationException, IllegalAccessException {
        int user_id = 2;
        ArrayList<Recipe> arr = getRecipeByUserID(user_id);

        System.out.println(arr.toString());

    } // end of the method.


    *//**
     * Searches the database for recipes that contain the keyword in their names.
     * If recipes are found returns array of recipes and throws exception otherwise.
     * @param keyword - the keyword
     * @return Recipe[] - the array of the recipe
     *//*
    public ResultSet searchByRecipe(String keyword)
    {
        Connection con = getCon();

        try {
            CallableStatement cStmt = con.prepareCall("{call searchByRecipes(?, ?)}");
            cStmt.setString(1, "%" + keyword + "%");
            cStmt.setInt(2, SEARCH_LIMIT);

            ResultSet rs = cStmt.executeQuery();
            //ArrayList<Recipe> arr = resultSetPacker(rs, Recipe.class);

            //return arr.toArray(new Recipe[arr.size()]);
            return rs;

        } catch (SQLException e) {
            System.out.println(e.getMessage());
            return null;
        }

    } // end of the method


    private void testsearchByRecipe()
    {
        ResultSet arr = searchByRecipe("tuna");
        ArrayList<Recipe> arr2 = resultSetPacker(arr, Recipe.class);

        showObject(arr2.toArray(new Recipe[arr2.size()]));

        //System.out.println(arr.toString());

    } // end of the method.


    *//**
     * Searches the database for recipes that contain the keyword in their names.
     * If recipes are found returns array of recipes and throws exception otherwise.
     * @param keyword - the keyword
     * @return Recipe[] - the array of the recipe
     *//*
    public ResultSet searchByIngredients(String keyword)
    {
        Connection con = getCon();

        try {
            CallableStatement cStmt = con.prepareCall("{call searchByIngredients(?, ?)}");
            cStmt.setString(1, "%" + keyword + "%");
            cStmt.setInt(2, SEARCH_LIMIT);

            ResultSet rs = cStmt.executeQuery();
            //ArrayList<Recipe> arr = resultSetPacker(rs, Recipe.class);

            //return arr.toArray(new Recipe[arr.size()]);
            return rs;

        } catch (SQLException e) {
            System.out.println(e.getMessage());
            return null;
        }

    } // end of the method


    private void testsearchByIngredients()
    {
        ResultSet arr = searchByIngredients("egg");
        ArrayList<Recipe> arr2 = resultSetPacker(arr, Recipe.class);

        showObject(arr2.toArray(new Recipe[arr2.size()]));

        //System.out.println(arr.toString());

    } // end of the method.


    *//**
     * Searches the database for recipes that an user has posted.
     * If recipes are found returns array of recipes and throws exception otherwise.
     * @param keyword - the keyword
     * @return Recipe[] - the array of the recipe
     *//*
    public ResultSet searchByMonster(String keyword)
    {
        Connection con = getCon();

        try {
            CallableStatement cStmt = con.prepareCall("{call searchByMonster(?, ?)}");
            cStmt.setString(1, "%" + keyword + "%");
            cStmt.setInt(2, SEARCH_LIMIT);

            ResultSet rs = cStmt.executeQuery();
            //ArrayList<Recipe> arr = resultSetPacker(rs, Recipe.class);

            //return arr.toArray(new Recipe[arr.size()]);
            return rs;

        } catch (SQLException e) {
            System.out.println(e.getMessage());
            return null;
        }

    } // end of the method


    private void testsearchByMonster()
    {
        ResultSet arr = searchByMonster("ahmad");
        ArrayList<Recipe> arr2 = resultSetPacker(arr, Recipe.class);

        showObject(arr2.toArray(new Recipe[arr2.size()]));

        //System.out.println(arr.toString());

    } // end of the method.


    *//**
     * Searches the database for recipes that contain the keyword in their names.
     * If recipes are found returns array of recipes and throws exception otherwise.
     * @param keyword - the keyword
     * @param pageSize - the page size
     * @return - the Cursor object
     *//*
    public Cursor searchByRecipeCur(String keyword)
    {
        Connection con = getCon();

        try {
            CallableStatement cStmt = con.prepareCall("{call searchByRecipes(?, ?)}");
            cStmt.setString(1, "%" + keyword + "%");
            cStmt.setInt(2, SEARCH_LIMIT);

            ResultSet rs = cStmt.executeQuery();

            return new Cursor(rs);

        } catch (SQLException e) {
            System.out.println(e.getMessage());
            return null;
        }

    } // end of the method


    private void testsearchByRecipeCur()
    {
        Cursor cur = searchByRecipeCur("tuna");

        showObject(cur.getArr());

        System.out.println(cur.toString());

        showObject(cur.nextPage());
        showObject(cur.nextPage());
        showObject(cur.nextPage());
        showObject(cur.previousPage());
        showObject(cur.previousPage());
        showObject(cur.previousPage());
        showObject(cur.nextPage());
        
        
    } // end of the method.


    *//**
     * Searches the database for recipes that contain the keyword in their names.
     * If recipes are found returns array of recipes and throws exception otherwise.
     * @param keyword - the keyword
     * @param pageSize - the page size
     * @return Recipe[] - the array of the recipe
     *//*
    public Cursor searchByIngredientsCur(String keyword)
    {
        Connection con = getCon();

        try {
            CallableStatement cStmt = con.prepareCall("{call searchByIngredients(?, ?)}");
            cStmt.setString(1, "%" + keyword + "%");
            cStmt.setInt(2, SEARCH_LIMIT);

            ResultSet rs = cStmt.executeQuery();
            return new Cursor(rs);

        } catch (SQLException e) {
            System.out.println(e.getMessage());
            return null;
        }

    } // end of the method


    private void testsearchByIngredientsCur()
    {
        Cursor cur = searchByIngredientsCur("egg");

        showObject(cur.getArr());

        System.out.println(cur.toString());

        showObject(cur.nextPage());
        showObject(cur.nextPage());
        showObject(cur.nextPage());
        showObject(cur.previousPage());
        showObject(cur.previousPage());
        showObject(cur.previousPage());
        showObject(cur.nextPage());

        //System.out.println(arr.toString());

    } // end of the method.


    *//**
     * Searches the database for recipes that an user has posted.
     * If recipes are found returns array of recipes and throws exception otherwise.
     * @param keyword - the keyword
     * @param pageSize - the page size
     * @return Recipe[] - the array of the recipe
     *//*
    public Cursor searchByMonsterCur(String keyword)
    {
        Connection con = getCon();

        try {
            CallableStatement cStmt = con.prepareCall("{call searchByMonster(?, ?)}");
            cStmt.setString(1, "%" + keyword + "%");
            cStmt.setInt(2, SEARCH_LIMIT);

            ResultSet rs = cStmt.executeQuery();
            return new Cursor(rs);

        } catch (SQLException e) {
            System.out.println(e.getMessage());
            return null;
        }

    } // end of the method



    private void testsearchByMonsterCur()
    {
        Cursor cur = searchByMonsterCur("Ahmad");

        showObject(cur.getArr());

        System.out.println(cur.toString());

        showObject(cur.nextPage());
        showObject(cur.nextPage());
        showObject(cur.nextPage());
        showObject(cur.previousPage());
        showObject(cur.previousPage());
        showObject(cur.previousPage());
        showObject(cur.nextPage());


        //System.out.println(arr.toString());

    } // end of the method.


    *//**
     * update the recipe rating.
     * @param newRate - the new rating of the recipe
     * @param recipe_id - the recipe id
     * @return true if the update is successful and false otherwish.
     *//*
    public boolean updateRatingByRecipeId (int newRate, int recipeId)
    {
        String stmnt = String.format(
                "UPDATE recipe "
                + "SET NoOfVotes = NoOfVotes+1, "
                + "RateSum = %d "
                + "WHERE ID = %d", newRate, recipeId);

        //Test!
        System.out.println("Statement: \n" + stmnt);

        int result = executeUpdate(stmnt);

        if (result == -1) return false;
        else              return true;

    } // end of the method

    private void testupdateRatingByRecipeId()
    {
        int newRate = 999;
        int recipeId = 114;

        System.out.println(updateRatingByRecipeId (newRate, recipeId));

    } // end of the method.


    *//**
     *//*
//........................ M A I N   M E T H O D ............................//
    *//**
     * This main method is just for testing this class.
     * @param args the arguments
     *//*
    public static void main(String[] args) 
    {
        RestaurantManager app = new RestaurantManager();
         .............................................

        app.testupdateRatingByRecipeId();

         .............................................
        app.releaseConnection();

    } // end of the main method.
*/
} // end of the class
